﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using Oracle.DataAccess.Client;

namespace Wicresoft.EKA.BOSync
{
    public class OracleDBHelper : IDB
    {
       private static readonly string  BOConnection=ConfigurationManager.ConnectionStrings["BOConnection"].ConnectionString;
        public string ConnectionString { get; set; }

        public OracleDBHelper()
            : this(BOConnection)
        {

        }
        public OracleDBHelper(string connString)
        {
            ConnectionString = connString;
        }



        public void ExecuteStoredProcedure(string spName)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = spName;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                //command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public void ExecuteStoredProcedure(string spName, DbParameter[] prams)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = spName;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                //command.CommandTimeout = 1800;

                if (prams != null && prams.Length > 0)
                {
                    command.Parameters.Clear();
                    command.Parameters.AddRange(prams.ToArray());
                }

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void ExecuteNonQuery(string sql)
        {
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                //command.CommandTimeout = 1800;

                try
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public IDataReader ExecuteReader(string sql, params DbParameter[] prams)
        {
            DbDataReader dbReader = null;
            DbConnection conn = new OracleConnection(ConnectionString);
            DbCommand command = conn.CreateCommand();
            command.CommandText = sql;
            command.CommandType = System.Data.CommandType.Text;
            if (prams != null)
                command.Parameters.AddRange(prams);
            //command.CommandTimeout = 1800;

            try
            {
                conn.Open();
                dbReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                throw;
            }
            return dbReader;
        }

        public string ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        public string ExecuteScalar(string sql, DbParameter[] prams)
        {
            string result;
            using (DbConnection conn = new OracleConnection(ConnectionString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                command.CommandType = System.Data.CommandType.Text;
                //command.CommandTimeout = 1800;

                if (prams != null && prams.Length > 0)
                {
                    command.Parameters.Clear();
                    foreach (var pram in prams)
                        command.Parameters.Add(pram);
                }

                try
                {
                    conn.Open();
                    object obj = command.ExecuteScalar();
                    if (obj == null)
                        result = string.Empty;
                    else
                        result = obj.ToString();
                }
                catch
                {
                    throw;
                }
                return result;
            }
        }

        public void ExecuteNonQuery(string sql, DbParameter[] prams, int? commandTimeout = null)
        {
            using (var conn = new OracleConnection(ConnectionString))
            {
                var _sql_command = new OracleCommand(sql, conn);
                _sql_command.CommandType = CommandType.Text;

                if (commandTimeout != null)
                {
                    _sql_command.CommandTimeout = commandTimeout.Value;
                }

                if (prams != null && prams.Length > 0)
                {
                    _sql_command.Parameters.Clear();
                    _sql_command.Parameters.AddRange(prams);
                }
                try
                {
                    conn.Open();
                    _sql_command.ExecuteNonQuery();
                }
                catch (OracleException)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public void ExecuteNonQuery(string sql, List<DbParameter[]> pramss, int? commandTimeout = null)
        {
            using (var conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                try
                {
                    if (pramss.Count > 0)
                    {
                        var _sql_command = new OracleCommand();
                        for (int i = 0; i < pramss.Count; i++)
                        {
                            _sql_command = new OracleCommand(sql, conn);
                            _sql_command.CommandType = CommandType.Text;

                            if (commandTimeout != null)
                            {
                                _sql_command.CommandTimeout = commandTimeout.Value;
                            }

                            if (pramss[i] != null && pramss[i].Length > 0)
                            {
                                _sql_command.Parameters.Clear();
                                foreach (var pram in pramss[i])
                                    _sql_command.Parameters.Add(pram);
                            }
                            _sql_command.ExecuteNonQuery();
                        }
                    }
                }
                catch (OracleException)
                {
                    throw;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public DataTable ExecuteDataTable(string sql)
        {
            //using (var conn = new OracleConnection(ConnectionString))
            //{
            //    OracleDataAdapter sqlDA = new OracleDataAdapter(sql, conn);
            //    DataTable dt = new DataTable("table");

            //    sqlDA.Fill(dt);

            //    return dt;
            //}
            return ExecuteDataTable(sql,null);
        }

        public DataTable ExecuteDataTable(string sql, params DbParameter[] prams)
        {
            DataTable table = new DataTable();
            using (var conn = new OracleConnection(ConnectionString))
            {
                OracleCommand cmd = new OracleCommand(sql, conn);
                //cmd.BindByName = true;

                if (prams != null && prams.Length > 0)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(prams);
                }
                OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                adapter.Fill(table);
            }
            return table;
        }

        public DataSet ExecuteDataSet(string sql)
        {
            using (var conn = new OracleConnection(ConnectionString))
            {
                OracleDataAdapter sqlDA = new OracleDataAdapter(sql, conn);
                DataSet ds = new DataSet();

                sqlDA.Fill(ds);

                return ds;
            }
        }

        public void BulkInsert<T>(IList<T> list)
        {
            using (var conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                OracleBulkCopy bc = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default);

                DataTable dt = list.ToDataTable();
                bc.DestinationTableName = typeof(T).Name;
                bc.WriteToServer(dt);
                bc.Close();

                conn.Close();
            }
        }




    }
}
